﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using ThinkNet.ORM;
using System.Linq;

namespace UnitTestNet
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            #region 写操作
            /*********************新增**************************/
            ////单条插入，不返回主键ID
            //var model1 = new Product() { Name = "叫哥给你糖吃1", Position = "职位", Company = "河姆渡", Adress = "上海", CardTypeId = 1, UserId = 1, IsSecrecy = true };
            //var result = ORM.Insert(model1);

            ////单条插入，返回主键ID
            //var model2 = new Product() { Name = "叫哥给你糖吃2", Position = "职位", Company = "河姆渡", Adress = "上海", CardTypeId = 1, UserId = 1, IsSecrecy = true };
            //var returnId = ORM.Insert(model2, true);

            ////批量插入，返回受影响行数
            //var list1 = new List<Product> {
            //    new Product() { Name = "test", Position = "职位77" },/*实体为空不插入*/
            //    new Product() { Name = "test", Position = "职位88", Company = "河姆渡2", Adress = "上海", CardTypeId = 1, UserId = 1, IsSecrecy = true },
            //    new Product() { Name = "test", Position = "职位99", Company = "河姆渡3", Adress = "上海", CardTypeId = 1, UserId = 1, IsSecrecy = true }
            //};
            //ORM.InsertBat(list1);


            /*********************修改**************************/
            //单条修改，默认根据主键ID修改
            //var model1 = new Product() { Id = 6, Position = "经理", Company = "河姆渡", Adress = "北京", CardTypeId = 0, UserId = 0, IsSecrecy = false };
            //ORM.Update(model1);

            ////条件修改，根据兰姆达表达式解析
            //var model2 = new Product() { Id = 6, Position = "经理2", Company = "河姆渡2", Adress = "北京2" };
            //int[] arr = new int[] { 7, 8, 9 };
            //ORM.Update(model2, it => it.Where(o => (o.Id.In(arr))));
            //ORM.Update(model2, it => it.Where(o => o.Position.Like("%经理%")));
            //ORM.Update(model2, it => it.Where(o => (o.Id == 8 && o.Position == "经理") || o.Position == "职位99"));

            ////批量修改，根据主键ID修改
            //var list1 = new List<Product> {
            //    new Product() { Id=7, Position = "经理", Company = "河姆渡", Adress = "北京", CardTypeId = 1, UserId = 1, IsSecrecy = true },
            //    new Product() { Id=8, Position = "组长", Company = "爱谱华顿", Adress = "深圳", CardTypeId = 1, UserId = 1, IsSecrecy = true },
            //    new Product() { Id=9, Position = "主管", Company = "阿里", Adress = "河南", CardTypeId = 1, UserId = 1, IsSecrecy = true }
            //};
            //ORM.UpdateBat(list1);


            /*********************删除**************************/
            ////逻辑删除，根据主键
            //var model1 = new Product() { Id = 2 };
            //ORM.LogicalDelete(model1);

            ////根据兰姆达条件逻辑删除
            //var model2 = new Product() { Id = 2 };
            //ORM.LogicalDelete(model2, it => it.Where(o => o.Position.Like("%主管%")));

            ////根据ID主键物理删除
            //ORM.Delete(new Product() { Id = 2 });

            ////根据兰姆达条件物理删除
            //ORM.Delete(new Product(), it => it.Where(x => x.Id == 3));
            #endregion

            #region 实体查询
            /*********************返回实体**************************/
            ////实体查询
            //var model1 = ORM.Get<Product>("10");
            ////返回指定类型的实体
            //var model2 = ORM.Get<Product, Product2>("10");

            /*********************返回列表**************************/
            ////列表查询
            //var list1 = ORM.Get<Product>(it => it.Where(o => o.Name == "test' OR 1=1)--'" && o.Position == "主管"));
            //var list = ORM.Get<Product>(it => it.Where(o => o.Id.In(1, 2, 4)));
            ////返回新实体列表
            //var list2 = ORM.Get<Product, Product2>(it => it.Where(o => o.Name == "test"));

            ////sql条件查询
            //var list3 = ORM.GetWhere<Product>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC");
            ////sql条件查询返回指定类型
            //var list4 = ORM.GetWhere<Product, Product2>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC");
            ////sql条件查询参数化，方式1
            //var list5 = ORM.GetWhere<Product>("WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", "*", new { Name = "test", UserId = 1 });
            //sql条件查询参数化，方式2
            ////查询参数
            //Dictionary<string, object> param = new Dictionary<string, object>();
            //param.Add("@Name", "test");
            //param.Add("@UserId", 1);
            //var list6 = ORM.GetWhere<Product>("WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", "*", param);

            ////执行sql查询
            //var list7 = ORM.RunSql<Product>("SELECT * FROM dbo.Product WHERE Name='test' AND UserId=1 ORDER BY Id ASC");
            #endregion

            #region 分页查询
            /*********************2008分页查询**************************/
            ////分页查询
            //var list1 = ORM.Get<Product>(it => it.Where(o => o.Mark > 0), 1, 2, "*", true, ESqlVersion.SqlServer2008);
            //var list11 = ORM.Get<Product>(it => it.Where(o => o.Mark > 0).OrderBy(o => o.Id), 1, 2, "*", true, ESqlVersion.SqlServer2008);
            ////分页查询，返回新集合
            //var list2 = ORM.Get<Product, Product2>(it => it.Where(o => o.Mark > 0).OrderBy(o => o.Id), 1, 2, "*", true, ESqlVersion.SqlServer2008);

            ////sql条件查询
            //var list3 = ORM.GetWhere<Product>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, "*", null, true, ESqlVersion.SqlServer2008);
            ////sql条件查询，返回新集合
            //var list4 = ORM.GetWhere<Product, Product2>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, "*", null, true, ESqlVersion.SqlServer2008);
            ////sql条件查询，参数化
            //var list5 = ORM.GetWhere<Product>("WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", 1, 2, "*", new { Name = "test", UserId = 1 }, true, ESqlVersion.SqlServer2008);

            ////sql查询分页
            //var list6 = ORM.RunSql<Product>("SELECT * FROM dbo.Product WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, null, ESqlVersion.SqlServer2008);
            ////sql查询分页，参数化
            //var list7 = ORM.RunSql<Product>("SELECT * FROM dbo.Product WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", 1, 2, new { Name = "test", UserId = 1 }, ESqlVersion.SqlServer2008);


            /*********************2012分页查询**************************/
            ////分页查询
            //var list1 = ORM.Get<Product>(it => it.Where(o => o.Mark > 0), 1, 2, "*", true, ESqlVersion.SqlServer2012);
            //var list11 = ORM.Get<Product>(it => it.Where(o => o.Mark > 0).OrderBy(o => o.Id), 1, 2, "*", true, ESqlVersion.SqlServer2012);
            ////分页查询，返回新集合
            //var list2 = ORM.Get<Product, Product2>(it => it.Where(o => o.Mark > 0).OrderBy(o => o.Id), 1, 2, "*", true, ESqlVersion.SqlServer2012);

            ////sql条件查询
            //var list3 = ORM.GetWhere<Product>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, "*", null, true, ESqlVersion.SqlServer2012);
            ////sql条件查询，返回新集合
            //var list4 = ORM.GetWhere<Product, Product2>("WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, "*", null, true, ESqlVersion.SqlServer2012);
            ////sql条件查询，参数化
            //var list5 = ORM.GetWhere<Product>("WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", 1, 2, "*", new { Name = "test", UserId = 1 }, true, ESqlVersion.SqlServer2012);

            ////sql查询分页
            //var list6 = ORM.RunSql<Product>("SELECT * FROM dbo.Product WHERE Name='test' AND UserId=1", "ORDER BY Id ASC", 1, 2, null, ESqlVersion.SqlServer2012);
            ////sql查询分页，参数化
            //var list7 = ORM.RunSql<Product>("SELECT * FROM dbo.Product WHERE Name=@Name AND UserId=@UserId", "ORDER BY Id ASC", 1, 2, new { Name = "test", UserId = 1 }, ESqlVersion.SqlServer2012);
            #endregion

            #region 统计
            ////Count计算
            //var count = ORM.Count<Product>(it => it.Where(o => o.Mark > 0));

            ////SUM计算
            //var sum = ORM.Sum<Product>(it => it.Where(o => o.Mark > 0), "UserId");
            #endregion
        }
    }
    public class Product
    {
        /// <summary>
        /// 获取主键列名
        /// </summary>
        public MPrimaryKey GetKeyName() { return new MPrimaryKey { KeyName = "Id", IsIncrement = true }; }
        public int? Id { get; set; }

        public string Name { get; set; }

        public string Position { get; set; }

        public string Company { get; set; }

        public string Adress { get; set; }

        public int? CardTypeId { get; set; }

        public int? UserId { get; set; }

        public bool? IsSecrecy { get; set; }

        public int? Mark { get; set; }
    }
    public class Product2
    {
        /// <summary>
        /// 获取主键列名
        /// </summary>
        public MPrimaryKey GetKeyName() { return new MPrimaryKey { KeyName = "Id", IsIncrement = true }; }
        public int? Id { get; set; }

        public string Name { get; set; }

        public string Position { get; set; }

        public string Company { get; set; }

        public string Adress { get; set; }

        public int? CardTypeId { get; set; }

        public int? UserId { get; set; }

        public bool? IsSecrecy { get; set; }

        public int? Mark { get; set; }
    }
}
